package actor

import (
	"coarse_score_cal_flow/middleware/mysql"
	"coarse_score_cal_flow/model"
	"database/sql"
	"fmt"
	"github.com/gin-gonic/gin"
	"gorm.io/gorm"
	"net/http"
	"time"
)

func CreateActor(c *gin.Context) {
	var actor model.Actor

	if err := c.ShouldBindJSON(&actor); err != nil {
		c.JSON(http.StatusOK, gin.H{"error": err.Error()})
		return
	}

	if err := mysql.DB.Create(&actor).Error; err != nil {
		c.JSON(http.StatusOK, gin.H{"error": err.Error()})
	} else {
		c.JSON(http.StatusOK, gin.H{"code": 200, "msg": "success", "data": &actor})
	}
}

// 根据主键查询第一条记录
// db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;

// 随机获取一条记录
// db.Take(&user)
// SELECT * FROM users LIMIT 1;

// 根据主键查询最后一条记录
// db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

// var actors []model.Actor
// 查询所有的记录
// db.Find(&users)
// SELECT * FROM users;

// 查询指定的某条记录(仅当主键为整型时可用)
// db.First(&user, 10)
// SELECT * FROM users WHERE id = 10;
func FirstActor(c *gin.Context) {
	var actor model.Actor
	mysql.DB.First(&actor)
	//
	c.JSON(http.StatusOK, gin.H{"actor": &actor})
}

func GetActor(c *gin.Context) {
	// var actor model.Actor
}

func Update(c *gin.Context) {
	var actor model.Actor
	actor.ID = 123
	mysql.DB.Model(&actor).Where("id = ?", 8).Update("name", "Bob")
	c.JSON(http.StatusOK, gin.H{"actor": &actor})
}

// 指定字段更新
func Updates(c *gin.Context) {
	var actor model.Actor
	mysql.DB.Model(&actor).Where("name = ?", "Echo").Where("age = ? ", 20).Updates(map[string]interface{}{"comment": "updates", "age": 19})

	// updates只会更新有变化且非零(0, "")的字段
	mysql.DB.Model(&actor).Where("name = ?", "Delta").Where("age = ? ", 20).Updates(model.Actor{Comment: "updates", Age: 19})
}

func UpdateSelectField(c *gin.Context) {
	var actor model.Actor
	mysql.DB.Model(&actor).Debug().Select("name").Where("id = ?", 9).
		Updates(map[string]interface{}{"name": "Omit", "comment": "updates", "age": 19})
}

func UpdateOmitField(c *gin.Context) {
	var actor model.Actor
	mysql.DB.Model(&actor).Debug().Omit("name").Where("id = ?", 10).
		Updates(map[string]interface{}{"name": "Omit", "comment": "updates", "age": 19})
}

/*
上面的更新操作会自动运行 model 的 BeforeUpdate, AfterUpdate 方法，更新 UpdatedAt 时间戳, 在更新时保存其 Associations, 如果你不想调用这些方法，你可以使用 UpdateColumn， UpdateColumns
*/
func UpdateWithoutHook(c *gin.Context) {
	var actor model.Actor
	// 更新单个属性，类似于 `Update`
	// UPDATE users SET name='hello' WHERE id = 111;
	rowsAffected1 := mysql.DB.Model(&actor).Where("id = ?", 11).UpdateColumn("name", "withouthook").RowsAffected

	// 更新多个属性，类似于 `Updates`
	// UPDATE users SET name='hello', age=18 WHERE id = 111;
	rowsAffected2 := mysql.DB.Model(&actor).Where("id = ?", 12).UpdateColumns(model.Actor{Name: "withouthook", Age: 18}).RowsAffected

	rsp := []int64{rowsAffected1, rowsAffected2}
	c.JSON(http.StatusOK, gin.H{"updateRes": &rsp})
}

func ExprOp(c *gin.Context) {
	var actor model.Actor
	mysql.DB.Model(&actor).Where("id = ?", 13).Update("age", gorm.Expr("age * ? + ?", 2, 3))
}

func DeleteActor(c *gin.Context) {
	var actor model.Actor
	actor.ID = 123
	mysql.DB.Delete(&actor)
}

func Debug(c *gin.Context) {
	actor := &model.Actor{Comment: "", Age: 20}
	mysql.DB.Debug().Create(&actor)
}

// 很简单，就是如果设置了默认值，那么你就没法直接写入零值了，因为零值都会被替换成默认值。如果真的想要写零值，就用这两种方式
func CreateEmptyFieldPointerActor(c *gin.Context) {
	var actor model.PointerActor
	actor = model.PointerActor{Name: new(string), Age: 41}

	if err := mysql.DB.Create(&actor).Error; err != nil {
		c.JSON(http.StatusOK, gin.H{"error": err.Error()})
	} else {
		c.JSON(http.StatusOK, gin.H{"code": 200, "msg": "success", "data": &actor})
	}
}

// sql.NullString: 实现了 value和scanner的接口
func CreateNullStringActor(c *gin.Context) {
	var actor model.NullStringActor
	actor = model.NullStringActor{Name: sql.NullString{"", true}, Age: 42}

	if err := mysql.DB.Create(&actor).Error; err != nil {
		c.JSON(http.StatusOK, gin.H{"error": err.Error()})
	} else {
		c.JSON(http.StatusOK, gin.H{"code": 200, "msg": "success", "data": &actor})
	}
}

func queryWhere(c *gin.Context) {
	// Get first matched record
	var actor1 model.Actor
	mysql.DB.Where("name = ?", "jinzhu").First(&actor1)
	// SELECT * FROM users WHERE name = 'jinzhu' limit 1;

	// Get all matched records
	var actors1 []model.Actor
	mysql.DB.Where("name = ?", "jinzhu").Find(&actors1)
	// SELECT * FROM users WHERE name = 'jinzhu';

	// <>
	var actors2 []model.Actor
	mysql.DB.Where("name <> ?", "jinzhu").Find(&actors2)
	// SELECT * FROM users WHERE name <> 'jinzhu';

	// IN
	var actors3 []model.Actor
	mysql.DB.Where("name IN (?)", []string{"jinzhu", "jinzhu 2"}).Find(&actors3)
	// SELECT * FROM users WHERE name in ('jinzhu','jinzhu 2');

	// LIKE
	var actors4 []model.Actor
	mysql.DB.Where("name LIKE ?", "%jin%").Find(&actors4)
	// SELECT * FROM users WHERE name LIKE '%jin%';

	// AND
	var actors5 []model.Actor
	mysql.DB.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&actors5)
	// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

	// Time
	var actors6 []model.Actor
	timeStr := "2000-01-01 12:00:00"

	// 解析字符串为时间
	lastWeek, err := time.Parse(time.DateTime, timeStr)
	if err != nil {
		fmt.Println("Error parsing time:", err)
		return
	}

	mysql.DB.Where("updated_at > ?", lastWeek).Find(&actors6)
	// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

	// BETWEEN
	var actors7 []model.Actor
	today := time.Now()
	mysql.DB.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&actors7)
	// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';
}

func queryStructMap(c *gin.Context) {
	// struct
	var actor1 model.Actor
	mysql.DB.Where(&model.Actor{Name: "Bob", Age: 20}).First(actor1)

	// map
	var actors1 []model.Actor
	mysql.DB.Where(map[string]interface{}{"name": "Bob", "age": 20}).Find(actors1)

	// 主键的切片
	var actors2 []model.Actor
	mysql.DB.Where([]int64{1, 2, 3}).Find(&actors2)
	// // SELECT * FROM users WHERE id IN (20, 21, 22);
}

type Result struct {
	Name   string
	SumAge int64
}

// Immediate methods ，立即执行方法是指那些会立即生成SQL语句并发送到数据库的方法, 他们一般是CRUD方法，比如：
// Create, First, Find, Take, Save, UpdateXXX, Delete, Scan, Row, Rows
func GroupByHaving(c *gin.Context) {
	rows, err := mysql.DB.Table("actor").Select("name, sum(age) as sum_age").Group("name").Having("sum(age) > ?", 10).Rows()
	if err != nil {
		fmt.Printf("GroupByHavingErr %v", err)
	}

	var rets1 []Result
	for rows.Next() {
		var ret Result
		if err := rows.Scan(&ret.Name, &ret.SumAge); err != nil {
			fmt.Printf("Scan Convert: %v\n", err)
			return
		}
		rets1 = append(rets1, ret)
	}

	// var rets2 []Result
	// mysql.DB.Table("actor").Select("name, sum(age) as sum_age").Group("name").Having("sum(age) > ?", 10).Scan(&rets2)
	c.JSON(http.StatusOK, gin.H{"code": 200, "msg": "success", "data": &rets1})
}

func Count(c *gin.Context) {
	var count int64
	mysql.DB.Table("actor").Where("name = ?", "Bob").Count(&count)
	c.JSON(http.StatusOK, gin.H{"code": 200, "msg": "success", "data": &count})
}

func SkipLimit(c *gin.Context) {
	var actors []model.Actor
	mysql.DB.Table("actor").
		Where("name = ?", "Bob").
		Offset(1).
		Limit(2).
		Order("age desc").
		Find(&actors)
	c.JSON(http.StatusOK, gin.H{"code": 200, "msg": "success", "data": &actors})
}

func AgeGreaterThan20(db *gorm.DB) *gorm.DB {
	return db.Where("age > ?", 20)
}

func NameAndAgeCond(names []string) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		return db.Scopes(AgeGreaterThan20).Where("name IN (?)", names)
	}
}

func Scopes(c *gin.Context) {
	var actors []model.Actor
	mysql.DB.Scopes(NameAndAgeCond([]string{"Alpha", "Bravo"})).Find(&actors)
	c.JSON(http.StatusOK, gin.H{"code": 200, "msg": "success", "data": &actors})
}

// Save 会保存所有的字段，即使字段是零值
// 保存 是一个组合函数。 如果保存值不包含主键，它将执行 Create，否则它将执行 Update (包含所有字段)。
func Save(c *gin.Context) {
	var actor = model.Actor{}
	actor.ID = 1
	actor.Name = "Trunk"
	mysql.DB.Save(&actor)
}

func Delete(c *gin.Context) {
	var actor = model.Actor{}
	actor.ID = 1
	mysql.DB.Debug().Delete(&actor)

	var res1Actor = model.Actor{}
	var res2Actor = model.Actor{}
	var res3Actor = model.Actor{}
	mysql.DB.Debug().Where("id = ?", 1).Find(&res1Actor)
	mysql.DB.Debug().Where("id = ?", 2).Find(&res2Actor)
	res3Actor.ID = 3
	mysql.DB.Unscoped().Delete(&res3Actor)
	var res4Actor = model.Actor{}
	mysql.DB.Debug().Where("id = ?", 3).Find(&res4Actor)

	c.JSON(
		http.StatusOK,
		gin.H{
			"code":  200,
			"msg":   "success",
			"data1": &res1Actor,
			"data2": &res2Actor,
			"data3": &res3Actor,
			"data4": &res4Actor,
		})
}

func DeleteBatch(c *gin.Context) {
	mysql.DB.Debug().Where("name LIKE ?", "%lph%").Delete(&model.Actor{})
	// DELETE from emails where email LIKE "%lph%";

	mysql.DB.Debug().Delete(&model.Actor{}, "name LIKE ?", "Br%")
	// DELETE from emails where email LIKE "Br%";
}
